
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 01/10/2013 00:13:12
-- Generated from EDMX file: C:\Develop\codeplex\freelance\Freejobber.Core\Data\db_Freejobber.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [db_Freejobber];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK__tbl_city__id_cou__34C8D9D1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tbl_city] DROP CONSTRAINT [FK__tbl_city__id_cou__34C8D9D1];
GO
IF OBJECT_ID(N'[dbo].[FK__tbl_city__id_reg__33D4B598]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tbl_city] DROP CONSTRAINT [FK__tbl_city__id_reg__33D4B598];
GO
IF OBJECT_ID(N'[dbo].[FK__tbl_regio__id_co__2E1BDC42]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tbl_region] DROP CONSTRAINT [FK__tbl_regio__id_co__2E1BDC42];
GO
IF OBJECT_ID(N'[DataStoreContainer].[FK__tbl_userc__city___38996AB5]', 'F') IS NOT NULL
    ALTER TABLE [DataStoreContainer].[tbl_usercities] DROP CONSTRAINT [FK__tbl_userc__city___38996AB5];
GO
IF OBJECT_ID(N'[DataStoreContainer].[FK__tbl_userc__user___37A5467C]', 'F') IS NOT NULL
    ALTER TABLE [DataStoreContainer].[tbl_usercities] DROP CONSTRAINT [FK__tbl_userc__user___37A5467C];
GO
IF OBJECT_ID(N'[dbo].[FK_tbl_UserProfile_tbl_Users1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tbl_UserProfile] DROP CONSTRAINT [FK_tbl_UserProfile_tbl_Users1];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[tbl_city]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_city];
GO
IF OBJECT_ID(N'[dbo].[tbl_country]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_country];
GO
IF OBJECT_ID(N'[dbo].[tbl_region]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_region];
GO
IF OBJECT_ID(N'[DataStoreContainer].[tbl_usercities]', 'U') IS NOT NULL
    DROP TABLE [DataStoreContainer].[tbl_usercities];
GO
IF OBJECT_ID(N'[dbo].[tbl_UserProfile]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_UserProfile];
GO
IF OBJECT_ID(N'[dbo].[tbl_Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'tbl_UserProfile'
CREATE TABLE [dbo].[tbl_UserProfile] (
    [Id] int  NOT NULL,
    [userId] int  NOT NULL,
    [email] nvarchar(128)  NOT NULL,
    [created] datetime  NULL,
    [last_activity] datetime  NULL,
    [profile_enabled] tinyint  NOT NULL,
    [description] nvarchar(256)  NULL,
    [photo] varbinary(max)  NULL,
    [is_pro] tinyint  NOT NULL,
    [pro_expired] datetime  NULL,
    [cell_phone1] nvarchar(16)  NULL,
    [cell_phone2] nvarchar(16)  NULL,
    [user_name] nvarchar(128)  NULL
);
GO

-- Creating table 'tbl_Users'
CREATE TABLE [dbo].[tbl_Users] (
    [Id] int  NOT NULL,
    [LoginName] nvarchar(128)  NOT NULL,
    [Password] nvarchar(128)  NOT NULL,
    [AdminLevel] tinyint  NOT NULL
);
GO

-- Creating table 'tbl_city'
CREATE TABLE [dbo].[tbl_city] (
    [id] int  NOT NULL,
    [id_region] int  NOT NULL,
    [id_country] int  NOT NULL,
    [oid] int  NOT NULL,
    [city_name_ru] varchar(255)  NULL,
    [city_name_en] varchar(255)  NOT NULL
);
GO

-- Creating table 'tbl_country'
CREATE TABLE [dbo].[tbl_country] (
    [id] int  NOT NULL,
    [oid] int  NOT NULL,
    [country_name_ru] varchar(50)  NOT NULL,
    [country_name_en] varchar(50)  NOT NULL
);
GO

-- Creating table 'tbl_region'
CREATE TABLE [dbo].[tbl_region] (
    [id] int  NOT NULL,
    [id_country] int  NOT NULL,
    [oid] int  NOT NULL,
    [region_name_ru] varchar(255)  NULL,
    [region_name_en] varchar(255)  NOT NULL
);
GO

-- Creating table 'tbl_usercities'
CREATE TABLE [dbo].[tbl_usercities] (
    [tbl_city_id] int  NOT NULL,
    [tbl_Users_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [userId] in table 'tbl_UserProfile'
ALTER TABLE [dbo].[tbl_UserProfile]
ADD CONSTRAINT [PK_tbl_UserProfile]
    PRIMARY KEY CLUSTERED ([userId] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_Users'
ALTER TABLE [dbo].[tbl_Users]
ADD CONSTRAINT [PK_tbl_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [id] in table 'tbl_city'
ALTER TABLE [dbo].[tbl_city]
ADD CONSTRAINT [PK_tbl_city]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'tbl_country'
ALTER TABLE [dbo].[tbl_country]
ADD CONSTRAINT [PK_tbl_country]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'tbl_region'
ALTER TABLE [dbo].[tbl_region]
ADD CONSTRAINT [PK_tbl_region]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [tbl_city_id], [tbl_Users_Id] in table 'tbl_usercities'
ALTER TABLE [dbo].[tbl_usercities]
ADD CONSTRAINT [PK_tbl_usercities]
    PRIMARY KEY NONCLUSTERED ([tbl_city_id], [tbl_Users_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [userId] in table 'tbl_UserProfile'
ALTER TABLE [dbo].[tbl_UserProfile]
ADD CONSTRAINT [FK_tbl_UserProfile_tbl_Users1]
    FOREIGN KEY ([userId])
    REFERENCES [dbo].[tbl_Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [id_country] in table 'tbl_city'
ALTER TABLE [dbo].[tbl_city]
ADD CONSTRAINT [FK__tbl_city__id_cou__34C8D9D1]
    FOREIGN KEY ([id_country])
    REFERENCES [dbo].[tbl_country]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__tbl_city__id_cou__34C8D9D1'
CREATE INDEX [IX_FK__tbl_city__id_cou__34C8D9D1]
ON [dbo].[tbl_city]
    ([id_country]);
GO

-- Creating foreign key on [id_region] in table 'tbl_city'
ALTER TABLE [dbo].[tbl_city]
ADD CONSTRAINT [FK__tbl_city__id_reg__33D4B598]
    FOREIGN KEY ([id_region])
    REFERENCES [dbo].[tbl_region]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__tbl_city__id_reg__33D4B598'
CREATE INDEX [IX_FK__tbl_city__id_reg__33D4B598]
ON [dbo].[tbl_city]
    ([id_region]);
GO

-- Creating foreign key on [id_country] in table 'tbl_region'
ALTER TABLE [dbo].[tbl_region]
ADD CONSTRAINT [FK__tbl_regio__id_co__2E1BDC42]
    FOREIGN KEY ([id_country])
    REFERENCES [dbo].[tbl_country]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__tbl_regio__id_co__2E1BDC42'
CREATE INDEX [IX_FK__tbl_regio__id_co__2E1BDC42]
ON [dbo].[tbl_region]
    ([id_country]);
GO

-- Creating foreign key on [tbl_city_id] in table 'tbl_usercities'
ALTER TABLE [dbo].[tbl_usercities]
ADD CONSTRAINT [FK_tbl_usercities_tbl_city]
    FOREIGN KEY ([tbl_city_id])
    REFERENCES [dbo].[tbl_city]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [tbl_Users_Id] in table 'tbl_usercities'
ALTER TABLE [dbo].[tbl_usercities]
ADD CONSTRAINT [FK_tbl_usercities_tbl_Users]
    FOREIGN KEY ([tbl_Users_Id])
    REFERENCES [dbo].[tbl_Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_tbl_usercities_tbl_Users'
CREATE INDEX [IX_FK_tbl_usercities_tbl_Users]
ON [dbo].[tbl_usercities]
    ([tbl_Users_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------